/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package QC_DI_Generator;

import java.awt.BorderLayout;
import java.awt.EventQueue;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import org.supercsv.io.CsvListReader;
import org.supercsv.prefs.CsvPreference;

/**
 *
 * @author laodiwen
 */
public class QC_DI_Generator_v1 {

    public static void main(String[] args) {

//        EventQueue.invokeLater(new Runnable() {
//
//            @Override
//            public void run() {
        Frame frame = new Frame();
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setVisible(true);
//            }
//        });
    }
}

class Frame extends JFrame {

    public static final int DEFAULT_WIDTH = 390;
    public static final int DEFAULT_HEIGHT = 510;

    public Frame() {
        setTitle("QC DI Generator v1.1");
        setSize(DEFAULT_WIDTH, DEFAULT_HEIGHT);
        setResizable(false);
        component component = new component();
        add(component);
    }
}

class component extends JPanel {

    private JFileChooser fc;
    private File output;
    private File input;
    private FileWriter fstream;
    private FileReader fr;
    private FileReader fr2;
    private BufferedWriter out;
    private BufferedReader in;
    private BufferedReader in2;
    private JButton button;
    private JPanel panel;
    private JTextArea log;
    private ArrayList<String> column;
    private ArrayList<String> stepDesc;
    private ArrayList<String> stepExpectedResults;
    private ArrayList<String> data;
    private int step = 0;
    private Calendar cal;
    private SimpleDateFormat dateFormatLocal;
    private String newline;
    private CsvListReader parser;
    private int fomularCount;
    private boolean fileExist;
    private int appendCount;
    private String str3 = "";
    private String str5 = "";

    public component() {
        fileExist = false;
        panel = new JPanel();
        panel.setLayout(new GridLayout(1, 2));
        data = new ArrayList<String>();
        column = new ArrayList<String>();
        stepDesc = new ArrayList<String>();
        stepExpectedResults = new ArrayList<String>();
        dateFormatLocal = new SimpleDateFormat("yyyy-MMM-dd HH:mm:ss");

//                              CDC/NFR
//        stepExpectedResults.add("Log on is successful.");
//        stepExpectedResults.add("Detailed log file is open.");
//        stepExpectedResults.add("Table exists and Row count is greater than zero.");
//        stepExpectedResults.add("Test script shows \"passed\" in the log file.");
//        stepExpectedResults.add("Defect is created in QC.");


//                               DI 
        stepExpectedResults.add("Log on is successful.");
        stepExpectedResults.add("Detailed log file is open.");
        stepExpectedResults.add("Row count is greater than zero.");
        stepExpectedResults.add("Test script shows \"passed\" in the log file.");


        newline = "\n" + "\n";
        button = new JButton("Generate");
        panel.add(button);
        button.addActionListener(new GenerateAction());
        fileExist = false;
        log = new JTextArea(27, 30);
        log.setLineWrap(true);
        log.setEditable(false);
        JScrollPane logScrollPane = new JScrollPane(log);
        add(panel, BorderLayout.CENTER);
        add(logScrollPane, BorderLayout.SOUTH);
    }

    private class GenerateAction implements ActionListener {

        public void actionPerformed(ActionEvent ae) {
            fc = new JFileChooser();
            int returnVal = fc.showOpenDialog(component.this);
            if (returnVal == JFileChooser.APPROVE_OPTION) {
                input = fc.getSelectedFile();
                returnVal = fc.showSaveDialog(component.this);
                if (returnVal == JFileChooser.APPROVE_OPTION) {
                    output = fc.getSelectedFile();
                    GenerateTemplate();
                }
            }
        }
    }

    private void GenerateTemplate() {
        log.setText("");
        fomularCount = 0;
        try {
            fr = new FileReader(input.getPath());
            in = new BufferedReader(fr);
            try {
                fstream = new FileWriter(output, true);

                if (output.exists()) {
                    fr2 = new FileReader(output.getPath());
                    in2 = new BufferedReader(fr2);
                    fileExist = true;
                    appendCount = 0;
                    String str = "";
                    while ((str = in2.readLine()) != null) {
                        appendCount++;
                    }
                    in2.close();
                }
                out = new BufferedWriter(fstream);
                String str4;
                str4 = output.getPath();
                if (fileExist == true) {
                    fomularCount = appendCount;
                    fileExist = false;
                }
                parser = new CsvListReader(new FileReader(input.getPath()), CsvPreference.EXCEL_PREFERENCE);
                List<String> values = parser.read();

                while (values != null) {
                    for (int x = 0; x < values.size(); x++) {
//                            if(x != 10){
                        data.add(values.get(x));
//                            }
                    }

                    if (data.get(1).equals("DI - DIM") || data.get(1).equals("DI - FACT")) {
                        str3 = "_MART";
                        str5 = "Data Mart";
                    } else {
                        str3 = "_DW";
                        str5 = "Data Warehouse";
                    }//comment for NFR/CDC ; uncomment for DI

                    System.out.println(data);

//                        CDC/NFR
//                    stepDesc.add("Log onto the Unix server.");
//                    stepDesc.add("Navigate to the directory with the PL SQL shell script log files. "
//                            + "Open the detailed log file for the current iteration.");
//                    stepDesc.add("\"(" + data.get(4) + ") Table validation: \n"
//                            + "1) Does the table exist?\n"
//                            + "2) Does the table have records?\"");
//                    stepDesc.add("\"Test Execution:\n"
//                            + "1) Determine the schema testing against (TDW or MART)\n"
//                            + "2) Determine the CDC type (TDW = Type 1 or non-transactional; MART = Type 1 or Type 2)\n"
//                            + "3) Execute test script (" + data.get(2) + str3 + ") SQL manually to validate table or determine failure cause\"");
//                    stepDesc.add("\"If test script fails, open a defect in QC and document failure cause.\"");



//                       DI
                    stepDesc.add("Log onto the Unix server.");
                    stepDesc.add("Navigate to the directory with the PL SQL shell script log files. "
                            + "Open the detailed log file for the current iteration.");
                    stepDesc.add("\"Locate the row count for table (" + data.get(4) + "). Confirm the table has been populated.\"");
                    stepDesc.add("\"In the detail log file, locate test script number in the defect log file.  (" + data.get(2) + str3
                            + ").  As a reminder... If the test script has failed:(1) rerun the SQL manually to determine failure cause.  "
                            + "(2) If you detect a defect, open the defect from this test set.\"");


                    try {
                        int stepCount = 4;  //5 for CDC/NFR    4 for DI
                        step = 0;

                        for (int x = 0; x < stepCount; x++) {

                            fomularCount++;
                            step = x + 1;
                            if (step == 1) {
                                column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"\\\"\",R" + fomularCount + ",\"\"\\\"\",S" + fomularCount + ")\"");//a
                                column.add(data.get(2) + str3);//b
////                            column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"_\"\",B" + fomularCount + ",\"\"_\"\",T" + fomularCount + ")\"");//c
                                column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"_\"\",T" + fomularCount + ")\"");//c
                                // column.add("\""+data.get(12)+"\"");//d
                                column.add("\"" + data.get(3) + "\"");//d
                                column.add("P2-High"); //e
                                column.add("Step " + step);// f
                                column.add(stepDesc.get(x));//g
                                column.add(stepExpectedResults.get(x));//h
                                column.add("");//i
                                column.add("");//j
                                column.add("");//k
                                column.add("");//l
                                column.add("");//m
                                column.add(data.get(5));//n
                                column.add("");//o
                                column.add("");//p

//                                column.add(str5);//q for DI
                                column.add(data.get(0));  // q for NFR

                                column.add(data.get(1));//r

//                                column.add("DI - " + data.get(4));//s for DI     
                                column.add("NFR - " + data.get(4));//s for NFR      

//                          column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"_\"\",B" + fomularCount + ",\"\"_\"\",C" + fomularCount + ")\"");//t
                                column.add(data.get(2) + str3);//t  

                                out.append(arrayToString(column) + "\n");
                                column.clear();
                            } else {
                                column.add("");//a
                                column.add("");//b
////                            column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"_\"\",B" + fomularCount + ",\"\"_\"\",T" + fomularCount + ")\"");//c
                                column.add("");//c
                                // column.add("\""+data.get(12)+"\"");//d
                                column.add("");//d
                                column.add(""); //e
                                column.add("Step " + step);// f
                                column.add(stepDesc.get(x));//g
                                column.add(stepExpectedResults.get(x));//h
                                column.add("");//i
                                column.add("");//j
                                column.add("");//k
                                column.add("");//l
                                column.add("");//m
                                column.add("");//n
                                column.add("");//o
                                column.add("");//p
                                column.add("");//q
                                column.add("");//r

                                column.add("");//s                       
//                                

//                          column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"_\"\",B" + fomularCount + ",\"\"_\"\",C" + fomularCount + ")\"");//t
                                column.add("");//t  
                                out.append(arrayToString(column) + "\n");
                                column.clear();
                            }

                        }
                        stepDesc.clear();
                    } catch (IOException ex) {
                        log.setText("");
                        cal = Calendar.getInstance();
                        log.setText("Error! Please close the opened Excel file    " + dateFormatLocal.format(cal.getTime()) + newline);
                    }
                    data.clear();
                    values = parser.read();
                }
                in.close();
                out.close();
                cal = Calendar.getInstance();
                log.append("Save to file: " + str4 + "    " + dateFormatLocal.format(cal.getTime()) + newline);
            } catch (IOException ex) {
                log.append("Error! Please make sure you have correct input file format" + "\n");
            }
        } catch (FileNotFoundException ex) {
            log.append("File not find: " + input + "\n");
        }
    }

    private String arrayToString(ArrayList<String> a) {
        String str = "";
        Iterator itr = a.iterator();
        while (itr.hasNext()) {
            str = str + itr.next() + ",";
        }
        return str;
    }
}
